﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
namespace PageAdmin.Utils.ORM
{
    /// <summary>
    /// 脚本，数据表，结构和字段有关的基础扩展方法
    /// </summary>
    public static class DbBasicMethod
    {

        #region 表和字段结构有关的基础扩展方法
        /// <summary>
        /// 运行sql脚本,采用命令
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="sqlPath"></param>
        public static void RunSqlScript(this DataBaseContext dbContext, string sqlScript)
        {
            //检测调用方是否开启了事务,避免事务重复
            bool transactioned = false;
            if (dbContext.TransactionState == TransactionStateEnum.Beign)
            {
                transactioned = true;
            }
            if (!string.IsNullOrEmpty(sqlScript))
            {
                if (!transactioned)
                {
                    dbContext.TransactionBegin();
                }
                try
                {
                    //阿里云虚拟主机不支持sqlScript.TrimStart()
                    string[] arrSql = Regex.Split(sqlScript.Trim(), @"^go(\r\n|\n)?", RegexOptions.Multiline | RegexOptions.IgnoreCase);
                    string sql = null;
                    foreach (string line in arrSql)
                    {
                        sql = line.Trim();
                        if (sql.StartsWith("/*"))
                        {
                            sql = Regex.Replace(sql, @"/\*.*?Script\s+Date.*?\*/(\r\n|\n)", "", RegexOptions.IgnoreCase).Trim();
                        }
                        if (string.IsNullOrEmpty(sql))
                        {
                            continue;
                        }
                        else
                        {
                           dbContext.Execute(sql);
                        }
                    }
                    if (!transactioned)
                    {
                        dbContext.TransactionCommit();
                    }
                }
                catch (SqlException ex)
                {
                    if (!transactioned)
                    {
                        dbContext.TransactionRollback();
                    }
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 去除空值数组
        /// </summary>
        /// <param name="ss"></param>
        /// <returns></returns>
        private static string[] filter(string[] ss)
        {
            List<string> strs = new List<string>();
            foreach (string s in ss)
            {
                if (!string.IsNullOrEmpty(s)) strs.Add(s);
            }
            string[] result = strs.ToArray();
            return result;
        }
        /// <summary>
        /// 将字符串分割成数组
        /// </summary>
        /// <param name="strSource"></param>
        /// <param name="strSplit"></param>
        /// <returns></returns>
        private static string[] StringSplit(string strSource, string strSplit)
        {
            string[] strtmp = new string[1];
            int index = strSource.IndexOf(strSplit, 0);
            if (index < 0)
            {
                strtmp[0] = strSource;
                return strtmp;
            }
            else
            {
                strtmp[0] = strSource.Substring(0, index);
                return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
            }
        }

        /// <summary>
        /// 采用递归将字符串分割成数组
        /// </summary>
        /// <param name="strSource"></param>
        /// <param name="strSplit"></param>
        /// <param name="attachArray"></param>
        /// <returns></returns>
        private static string[] StringSplit(string strSource, string strSplit, string[] attachArray)
        {
            string[] strtmp = new string[attachArray.Length + 1];
            attachArray.CopyTo(strtmp, 0);

            int index = strSource.IndexOf(strSplit, 0);
            if (index < 0)
            {
                strtmp[attachArray.Length] = strSource;
                return strtmp;
            }
            else
            {
                strtmp[attachArray.Length] = strSource.Substring(0, index);
                return StringSplit(strSource.Substring(index + strSplit.Length), strSplit, strtmp);
            }
        }



        ///// <summary>
        ///// 生成整个数据库结构的sql脚本，不包含数据脚本,依赖smo库
        ///// </summary>
        ///// <param name="dbContext"></param>
        //public static string BuildSqlScript(this DataBaseContext dbContext)
        //{
        //    StringBuilder stringBuilder = new StringBuilder();
        //    DataBaseInfo dbInfo = dbContext.DbInfo;
        //    Server server = new Server(new ServerConnection(dbInfo.DbServer, dbInfo.DbUser, dbInfo.DbPassword));
        //    Database db = server.Databases[dbInfo.DbName];
        //    //初始化Scripter 
        //    Scripter scripter = new Scripter();
        //    scripter.Options.Add(ScriptOption.DriAllConstraints);
        //    scripter.Options.Add(ScriptOption.DriAllKeys);
        //    scripter.Options.Add(ScriptOption.Default);
        //    scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
        //    scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
        //    scripter.Options.Add(ScriptOption.IncludeIfNotExists);

        //    scripter.Options.Add(ScriptOption.DriAllConstraints);
        //    scripter.Options.Add(ScriptOption.DriAllKeys);
        //    scripter.Options.Add(ScriptOption.Default);
        //    scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
        //    scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
        //    scripter.Options.Add(ScriptOption.IncludeIfNotExists);
        //    scripter.Options.Add(ScriptOption.IncludeDatabaseContext);

        //    scripter.Server = server;
        //    foreach (Table tb in db.Tables)
        //    {
        //        UrnCollection collection = new UrnCollection();
        //        collection.Add(tb.Urn);
        //        var sqls = scripter.Script(collection);
        //        foreach (var s in sqls)
        //        {
        //            stringBuilder.Append(s);
        //            stringBuilder.Append("\r\n");
        //        }
        //    }
        //    return stringBuilder.ToString();
        //}


        ///// <summary>
        ///// 生成单表的sql脚本,不包含数据脚本，表不存在则生成Null，,依赖smo库
        ///// </summary>
        ///// <param name="dbContext">DataBaseContext对象</param>
        ///// <param name="tableName">表名称</param>
        ///// <returns></returns>
        //public static string BuildSqlScript(this DataBaseContext dbContext,string tableName)
        //{
        //    StringBuilder stringBuilder = new StringBuilder();
        //    DataBaseInfo dbInfo = dbContext.DbInfo;
        //    Server server = new Server(new ServerConnection(dbInfo.DbServer, dbInfo.DbUser,dbInfo.DbPassword));
        //    if(!dbContext.TableIsExists(tableName))
        //    {
        //        return null;
        //    }
        //    Database db = server.Databases[dbInfo.DbName];
        //    Table table = db.Tables[tableName];
        //    UrnCollection collection = new UrnCollection();
        //    //初始化Scripter https://technet.microsoft.com/zh-cn/library/microsoft.sqlserver.management.smo.scriptoption.aspx/css
        //    Scripter scripter = new Scripter();
        //    scripter.Options.Add(ScriptOption.DriAllConstraints);
        //    scripter.Options.Add(ScriptOption.DriAllKeys);
        //    scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
        //    scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
        //    scripter.Options.Add(ScriptOption.IncludeIfNotExists);
        //    scripter.Options.Add(ScriptOption.NoCollation);
        //    scripter.Options.Remove(ScriptOption.ScriptOwner);
        //    scripter.Options.Add(ScriptOption.IncludeDatabaseContext);
        //    scripter.Server = server;
        //    collection.Add(table.Urn);
        //    var sqls = scripter.Script(collection);
        //    foreach (var s in sqls)
        //    {
        //        stringBuilder.Append(s);
        //        stringBuilder.Append("\r\n");
        //    }
        //    return stringBuilder.ToString();
        //}

        /// <summary>
        /// 获取数据库中所有用户表
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <returns></returns>
        public static List<string> GetAllTables(this DataBaseContext dbContext)
        {
            IDbInstance db = dbContext.DbInstance;
            return db.GetTables();
        }

        /// <summary>
        /// 获取表的结构数据
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public static DataTable GetSchemaTable(this DataBaseContext dbContext, string table)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            IDbInstance db = dbContext.DbInstance;
            return db.GetSchemaTable(table);
        }

        /// <summary>
        /// 检测表是否存在数据库中。
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>>
        /// <returns></returns>
        public static bool TableIsExists(this DataBaseContext dbContext, string table)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            IDbInstance db = dbContext.DbInstance;
            return db.TableIsExists(table);
        }

        /// <summary>
        /// 检测字段是否存在数据表中
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="field">字段</param>
        /// <returns></returns>
        public static bool ColumnIsExists(this DataBaseContext dbContext, string table, string field)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            IDbInstance db = dbContext.DbInstance;
            return db.ColumnIsExists(table, field);
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfoList"></param>
        public static void CreateTable(this DataBaseContext dbContext, string tableName, IEnumerable<TableColumnInfo> tableColumnInfoList)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.CreateTable(tableName, tableColumnInfoList);
        }

        /// <summary>
        /// 创建字段
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public static void CreateColumn(this DataBaseContext dbContext, string tableName, TableColumnInfo tableColumnInfo)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.CreateColumn(tableName, tableColumnInfo);
        }


        /// <summary>
        ///  删除表
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="table"></param>
        public static void DropTable(this DataBaseContext dbContext, string table)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            db.DropTable(table);
        }

        /// <summary>
        /// 删除字段，内部已经对默认值约束进行了删除处理，不需要在调用默认值删除方法。
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        public static void DropColumn(this DataBaseContext dbContext, string tableName, string fieldName)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.DropColumn(tableName, fieldName);
        }

        /// <summary>
        /// 修改表名
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="name"></param>
        /// <param name="targetName"></param>
        public static void RenameTable(this DataBaseContext dbContext, string name, string targetName)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                name = schmea + "." + name;
                targetName = schmea + "." + targetName;
            }
            db.RenameTable(name, targetName);
        }

        /// <summary>
        /// 修改字段名
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="name"></param>
        /// <param name="targetName"></param>
        public static void RenameColumn(this DataBaseContext dbContext, string tableName, string name, string targetName)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.RenameColumn(tableName, name, targetName);
        }

        /// <summary>
        /// 修改字段属性，修改会导致默认值丢失（内部调用了默认值删除方法），请在tableColumnInfo中设置好新的默认值
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public static void AlterColumn(this DataBaseContext dbContext, string tableName, TableColumnInfo tableColumnInfo)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.AlterColumn(tableName, tableColumnInfo);
        }


        /// <summary>
        /// 设置字段默认值，需要先删除旧的默认值才能添加。
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public static void AlterColumnDefaultValue(this DataBaseContext dbContext, string tableName, TableColumnInfo tableColumnInfo)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.AlterColumnDefaultValue(tableName, tableColumnInfo);
        }

        /// <summary>
        /// 删除默认值约束
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        public static void RemoveColumnDefaultValue(this DataBaseContext dbContext, string tableName, string fieldName)
        {
            IDbInstance db = dbContext.DbInstance;
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                tableName = schmea + "." + tableName;
            }
            db.RemoveColumnDefaultValue(tableName, fieldName);
        }

        #endregion

        /// <summary>
        /// 复制表，此操作会新建一个新的表，注意：此方法不复制字段默认值，将会内部根据字段类型重新创建默认值。
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sourceTable">源表</param>
        /// <param name="targetTable">目标表</param>
        /// <param name="copyData">是否复制数据</param>
        public static void CopyTable(this DataBaseContext dbContext, string sourceTable, string targetTable, bool copyData = false)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                sourceTable = schmea + "." + sourceTable;
                targetTable = schmea + "." + targetTable;
            }
            string sql = "select * into [dbo].[" + targetTable + "]  from [" + sourceTable + "]";
            if (!copyData)
            {
                sql += " where 1=2";
            }
            dbContext.Execute(sql);

            //添加主键
            sql = "ALTER TABLE [dbo].[" + targetTable + "] ADD CONSTRAINT PK_" + targetTable + "_id PRIMARY KEY(id)";
            dbContext.Execute(sql);

            //添加默认值
            IDbInstance db = dbContext.DbInstance;
            DataTable dt = dbContext.GetSchemaTable(targetTable);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                DataColumn column = dt.Columns[i];
                string field = column.ColumnName;
                if (field.Equals("id", StringComparison.OrdinalIgnoreCase))
                {
                    continue;
                }
                DataBaseType dataBaseType = dbContext.DbType;
                string dataType = column.DataType.ToString().Replace("System.", "").ToLower();
                TableColumnInfo tableColumnInfo = null;
                #region 构造sql语句
                switch (dataType)
                {
                    case "decimal":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.Decimal);
                        tableColumnInfo.DefaultValue = 0;
                        break;

                    case "int64":
                    case "long":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.BigInt);
                        tableColumnInfo.DefaultValue = 0;
                        break;

                    case "int":
                    case "int32":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.Int);
                        tableColumnInfo.DefaultValue = 0;
                        break;

                    case "short":
                    case "int16":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.SmallInt);
                        tableColumnInfo.DefaultValue = 0;
                        break;

                    case "tinyint":
                    case "sbyte":
                    case "byte":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.TinyInt);
                        tableColumnInfo.DefaultValue = 0;
                        break;


                    case "single":
                    case "float":
                    case "double":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.Float);
                        tableColumnInfo.DefaultValue = 0;
                        break;

                    case "datetime":
                        tableColumnInfo = new TableColumnInfo(field, ColumnDataType.DateTime);
                        tableColumnInfo.DefaultValue = "";
                        break;
                    case "string":
                        break;
                }
                if (tableColumnInfo != null)
                {
                    dbContext.AlterColumnDefaultValue(targetTable, tableColumnInfo);
                }
                #endregion
            }
        }

        /// <summary>
        /// 获取表的字段集合
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="table"></param>
        /// <param name="includeId"></param>
        /// <returns></returns>
        public static IEnumerable<string> GetTableFields(this DataBaseContext dbContext, string table, bool includeId = false)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + table;
            }
            string cacheKey = $"TableFields_{table}";
            if (CacheHelper.Contains("cacheKey"))
            {
                return CacheHelper.Get<IEnumerable<string>>(cacheKey);
            }
            List<string> list = new List<string>();
            DataTable dt = dbContext.GetSchemaTable(table);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                DataColumn column = dt.Columns[i];
                string field = column.ColumnName;
                if (!includeId)
                {
                    if (field.Equals("id", StringComparison.OrdinalIgnoreCase))
                    {
                        continue;
                    }
                }
                list.Add(field);
            }
            CacheHelper.Add(cacheKey, list, 600);
            return list;
        }

        /// <summary>
        /// 移除字段集合缓存
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static void RemoveTableFieldsCache(string table)
        {
            string cacheKey = $"TableFields_{table}";
            if (CacheHelper.Contains("cacheKey"))
            {
                CacheHelper.Remove(cacheKey);
            }
        }

    }
}